const DB = require('../../utils/db');
const os = require('os');

module.exports = {
	tabList: async () => {
		// 全部访客
		let [visitor] = await DB.query('select count(1) as total from t_visitor');
		let [currnetVisitor] = await DB.query("select count(1) as total from t_visitor where date_format(updateTime,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d')");

		// 评论数
		let [coment] = await DB.query('select count(1) as total from t_comment');
		let [currnetComent] = await DB.query("select count(1) as total from t_comment where date_format(createTime,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d')");

		// 留言数

		let [msg] = await DB.query('select count(1) as total from t_msg');
		let [currnetMsg] = await DB.query("select count(1) as total from t_msg where date_format(createTime,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d')");

		// 容量

		return {
			visitor: visitor[0].total,
			currnetVisitor: currnetVisitor[0].total,
			coment: coment[0].total,
			currnetComent: currnetComent[0].total,
			msg: msg[0].total,
			currnetMsg: currnetMsg[0].total,
			totalmem: os.totalmem() / 1024 / 1024 / 1024,
			freemem: os.freemem() / 1024 / 1024 / 1024
		};
	},
	echart: async () => {
		// 每周的访客数 dayofweek 1 表示星期日，2 表示星期一，依此类推，7 表示星期六。
		let [visitor] = await DB.query(
			`
                  select dayofweek(updateTime) as week, count(*) as num from t_visitor where  
                  YEARWEEK(updateTime, 1) = YEARWEEK(CURDATE(), 1)
                  group by dayofweek(updateTime)
                  `
		);

		let visitorList = [0, 0, 0, 0, 0, 0, 0];
		visitor.forEach((v) => (visitorList[v.week - 1] = v.num));

		// 留言
		let [msg] = await DB.query(
			`
                  select dayofweek(createTime) as week, count(*) as num from t_msg where  
                  YEARWEEK(createTime, 1) = YEARWEEK(CURDATE(), 1)
                  group by dayofweek(createTime)
                  `
		);

		let msgList = [0, 0, 0, 0, 0, 0, 0];
		msg.forEach((v) => (msgList[v.week - 1] = v.num));

		// 评论
		let [coment] = await DB.query(
			`
                  select dayofweek(createTime) as week, count(*) as num from t_comment where  
                  YEARWEEK(createTime, 1) = YEARWEEK(CURDATE(), 1)
                  group by dayofweek(createTime)
                  `
		);

		let comentList = [0, 0, 0, 0, 0, 0, 0];
		coment.forEach((v) => (comentList[v.week - 1] = v.num));

		// 城市

		let [list] = await DB.query('select city as name,count(*) as value from t_visitor group by city');
		return {
			city: list,
			week: [
				{ name: '访客', list: visitorList },
				{ name: '留言', list: msgList },
				{ name: '评论', list: comentList }
			]
		};
	}
};
